<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

use App\Models\User;
use App\Mail\subscribe;
use Illuminate\Support\Facades\Mail;

use App\Jobs\SendEmail;

class IndexController extends Controller
{
    //首页
    public function index()
    {
        $tdk = [];
        $tdk['title'] = 'PHP编程_互联网_常用PHP框架_前端后端分享交流-侯体宗的博客';
        $tdk['keywords'] = 'PHP编程_互联网_常用PHP框架_前端后端分享交流';
        $tdk['description'] = '个人博客,侯体宗的博客,PHP编程,centos,Thinkphp,laravel,数据库,layui,easyui,bootstrap,侯体宗的博客项目架构分享,侯体宗的博客常用框架及前端后端分享交流';

        $q = request()->input('q');

        //分类
        $cats = DB::table('cat')->join('art', 'art.cat_id', '=', 'cat.cat_id')
            ->select('cat.cat_id','cat.catname',DB::raw('count(art.cat_id) as num'))
            ->groupBy('art.cat_id')->get();

        //文章
        if (isset($q)) {
            if ($q == 'hot') {
                $art = DB::table('art')->select('art_id', 'title', 'content', 'pubtime', 'view', 'cat_id')->where('is_state', 0)->where('is_del', 1)->orderBy('view', 'desc')->paginate(35);
            }else {
                $art = DB::table('art')->select('art_id', 'title', 'content', 'pubtime', 'view', 'cat_id')->where('is_state', 0)->where('is_del', 1)->where('title','like',"%{$q}%")->paginate(35);
            }
        }else{
            $art = DB::table('art')->select('art_id','title','content','pubtime','view','cat_id')->where('is_state',0)->where('is_del',1)->orderBy('art_id','desc')->paginate(35);
        }

        //近期评论 取出10条
       // $sql = 'select t1.nick,t1.comment,t1.art_id,t2.title from comment as t1 inner join art as t2 on t1.art_id=t2.art_id order by t1.pubtime desc limit 5';


        //文章归档 格式：2017年01月
        //$sql = "select pubtime,FROM_UNIXTIME( `pubtime`,'%Y-%m') as time ,count(*) as num FROM art where  is_state=0 and is_del=1 group by time" ;
        $file = DB::table('art')->select('pubtime',DB::raw('FROM_UNIXTIME(pubtime,\'%Y-%m\') as time'),DB::raw('count(*) as num'))->where('is_state',0)->where('is_del',1)->groupBy('time')->get();

        //标签
        //$tags = Db::name('tag')->group('tagname')->order('tag_id desc')->limit(50)->column('tagname','tag_id');
        $tags = DB::table('tag')->select('*')->groupBy('tagname')->orderBy('tag_id','desc')->take(50)->pluck('tagname','tag_id');


        return view('home.index.index', ['tdk'=>$tdk,'tags'=>$tags,'file'=>$file,'art'=>$art,'cats'=>$cats]);
    }

    //订阅
    public function subscribe()
    {
        $data = request()->input();
        if ($data) {
            $res = DB::table('users')->where('id', $data['id'])->update(['subscribe' => $data['issub']]);
            if ($res) {
                echo json_encode(array('error' => 1, 'info' => '订阅成功'));
            }else {
                echo json_encode(array('error' => 0, 'info' => '订阅失败'));
            }
        }
    }
    //发订阅邮件
    public function sendsubscribeemail(Request $request, $id=1)
    {
        $user = User::findOrFail($id);
        $art = DB::table('art')->select('art_id', 'title', 'content')
            ->where('is_state', 0)->where('is_del', 1)
            ->whereRaw('YEARWEEK( FROM_UNIXTIME( pubtime, \'%Y-%m-%d\' ) ) = YEARWEEK(now())-3')
            ->orderBy('view', 'desc')->take(1)->get()->toJson();
        dd($art);
        //发邮件
        //Mail::to('houtizong@dingtalk.com')->send(new subscribe($user));
        return (new subscribe($user))->render();
    }
    //需要发邮件的订阅 插入队列
    public function addqueuesubscribe()
    {
        //   addqueuesubscribe?dy=htzsub
        $dy = request()->input('dy');
        if (!isset($dy) || $dy != 'htzsub') {
            return '瞎搞';
        }
        $user = DB::table('users')->where('subscribe',1)->get();
        //dd($user);
        foreach($user as $data){
            dispatch(new SendEmail($data->name,$data->email));
            echo $data->name.'<br>';
        }
    }



}
